/******************************************************************************
 * Copyright (C) 2013 - 2018 ShenZhen OnePlus Technology Co.,Ltd
 * All Rights Reserved.
 * 本软件为深圳万普拉斯科技有限公司开发研制。未经本公司正式书面同意，其他任何个人、团体不得使用、复制、修改或发布本软件.
 *****************************************************************************/
package com.data.reports.manager;

import com.data.reports.manager.database.DataSourceContextHolder;
import org.apache.commons.lang.StringUtils;
import org.beetl.core.Configuration;
import org.beetl.core.GroupTemplate;
import org.beetl.core.Template;
import org.beetl.core.resource.StringTemplateResourceLoader;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.jdbc.support.rowset.SqlRowSet;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * 报表增删改查实现
 *
 * @author Hurricane.Hu
 * @version V1.0
 * @Title: JdbcSqlConfigService.java
 * @Package com.data.com.data.reports.manager
 * @Description
 * @date 2018 03-12 16:38.
 */
public class JdbcSqlConfigService implements SqlConfigService {

    private final static Logger LOGGER = LoggerFactory.getLogger(JdbcSqlConfigService.class);

    private static String TABLE_NAME = "SQL_REPORTS_CONFIG";

    private static final String ADD_CONFIG = "INSERT INTO " + TABLE_NAME + " (db_code,config_code,report_name,report_desc,sql_content,report_type,status,create_user,create_time)" +
            " VALUES(?, ?, ? , ? , ?, ?, ?, ?, ROUND(UNIX_TIMESTAMP(NOW(4))*1000))";

    private static final String DELETE_CONFIG = "DELETE FROM " + TABLE_NAME + " WHERE id=?";

    private static final String GET_BY_ID = "SELECT * FROM " + TABLE_NAME + " WHERE id=?";

    private static final String GET_BY_CODE = "SELECT * FROM " + TABLE_NAME + " WHERE config_code=?";

    private static final String CREATE_TABLE = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " (" +
            "`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键'," +
            "`db_code` varchar(100) DEFAULT NULL COMMENT '数据源唯一编码'," +
            "`config_code` varchar(100) DEFAULT NULL COMMENT '唯一编码'," +
            "`report_name` varchar(100) DEFAULT NULL COMMENT '报表名称'," +
            "`report_desc` varchar(255) DEFAULT NULL COMMENT '报表描述'," +
            "`sql_content` text DEFAULT NULL COMMENT 'SQL执行内容'," +
            "`report_type` varchar(40) DEFAULT NULL COMMENT '报表类型'," +
            "`status` int(2) DEFAULT 0 COMMENT '状态'," +
            "`create_user` varchar(80) DEFAULT NULL COMMENT '创建人'," +
            "`create_time` bigint(20) DEFAULT NULL COMMENT '创建时间'," +
            "`modify_user` varchar(80) DEFAULT NULL COMMENT '更新人'," +
            "`modify_time` bigint(20) DEFAULT NULL COMMENT '更新时间'," +
            "PRIMARY KEY (`id`)" +
            ") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='报表配置'";

    private static final String CHECK_EXISTS = "SHOW TABLES LIKE '" + TABLE_NAME + "'";

    private static volatile boolean isTableExists;

    private JdbcTemplate jdbcTemplate;

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    private static String getUpdateSqlAndArgs(SqlReportConfig config, List<Object> args) {
        if (config.getId() == null) {
            throw new IllegalArgumentException("id is null.");
        }
        StringBuilder sql = new StringBuilder("UPDATE "
                + TABLE_NAME
                + " SET modify_time=ROUND(UNIX_TIMESTAMP(now(4))*1000)");
        if (StringUtils.isNotBlank(config.getConfigCode())) {
            sql.append(",config_code=?");
            args.add(config.getConfigCode());
        }
        if (StringUtils.isNotBlank(config.getDbCode())) {
            sql.append(",db_code=?");
            args.add(config.getDbCode());
        }
        if (StringUtils.isNotBlank(config.getReportName())) {
            sql.append(",report_name=?");
            args.add(config.getReportName());
        }
        if (StringUtils.isNotBlank(config.getReportDesc())) {
            sql.append(",report_desc=?");
            args.add(config.getReportDesc());
        }
        if (StringUtils.isNotBlank(config.getSqlContent())) {
            sql.append(",sql_content=?");
            args.add(config.getSqlContent());
        }
        if (StringUtils.isNotBlank(config.getReportType())) {
            sql.append(",report_type=?");
            args.add(config.getReportType());
        }
        if (config.getStatus() != null) {
            sql.append(",status=?");
            args.add(config.getStatus());
        }
        sql.append(" WHERE id=?");
        args.add(config.getId());
        return sql.toString();
    }

    private static String getQueryPageSqlAndArgs(Map<String, String> parameters, List<Object> args) {
        StringBuilder sql = new StringBuilder("SELECT * FROM " + TABLE_NAME + " WHERE 1=1 ");
        if (parameters == null || parameters.size() <= 0 || args == null) {
            return sql.toString();
        }
        if (StringUtils.isNotBlank(parameters.get("configCode"))) {
            sql.append(" AND config_code=? ");
            args.add(parameters.get("configCode"));
        }
        if (StringUtils.isNotBlank(parameters.get("dbCode"))) {
            sql.append(" AND db_code=? ");
            args.add(parameters.get("dbCode"));
        }
        if (StringUtils.isNotBlank(parameters.get("reportName"))) {
            sql.append(" AND report_name=? ");
            args.add(parameters.get("reportName"));
        }
        if (StringUtils.isNotBlank(parameters.get("reportDesc"))) {
            sql.append(" AND report_desc=? ");
            args.add(parameters.get("reportDesc"));
        }
        if (StringUtils.isNotBlank(parameters.get("reportType"))) {
            sql.append(" AND report_type=? ");
            args.add(parameters.get("reportType"));
        }
        if (StringUtils.isNotBlank(parameters.get("status"))) {
            sql.append(" AND status=? ");
            args.add(parameters.get("status"));
        }
        sql.append(" LIMIT ?,? ");
        int currentPage = StringUtils.isBlank(parameters.get("page")) ? 1 : Integer.parseInt(parameters.get("page"));
        int pageSize = StringUtils.isBlank(parameters.get("perPageCount")) ? 15 : Integer.parseInt(parameters.get("perPageCount"));
        int start = (currentPage - 1) * pageSize;
        args.add(start);
        args.add(pageSize);
        return sql.toString();
    }

    private void createTableNX() {

        if (isTableExists) {
            return;
        }
        isTableExists = jdbcTemplate.query(CHECK_EXISTS, new ResultSetExtractor<Boolean>() {
            public Boolean extractData(ResultSet resultSet) throws SQLException, DataAccessException {
                return resultSet.next();
            }
        });
        if (!isTableExists) {
            if (LOGGER.isDebugEnabled()) {
                LOGGER.debug("创建报表配置表成功.");
            }
            jdbcTemplate.execute(CREATE_TABLE);
        }
    }

    public void init() {//初始化建表，如果表不存在
        createTableNX();
    }

    public Integer addSqlReportConfig(final SqlReportConfig config) {

        checkCode(config.getConfigCode());
        KeyHolder holder = new GeneratedKeyHolder();
        if (config.getStatus() == null) {
            config.setStatus(0);
        }
        jdbcTemplate.update(new PreparedStatementCreator() {

            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement preparedStatement = connection.prepareStatement(ADD_CONFIG, Statement.RETURN_GENERATED_KEYS);
                preparedStatement.setString(1, config.getDbCode());
                preparedStatement.setString(2, config.getConfigCode());
                preparedStatement.setString(3, config.getReportName());
                preparedStatement.setString(4, config.getReportDesc());
                preparedStatement.setString(5, config.getSqlContent());
                preparedStatement.setString(6, config.getReportType());
                preparedStatement.setInt(7, config.getStatus());
                preparedStatement.setString(8, config.getCreateUser());
                return preparedStatement;
            }
        }, holder);

        return holder.getKey().intValue();
    }

    private void checkCode(String configCode) {
        SqlReportConfig existsConfig = getSqlReportConfigByCode(configCode);
        if (existsConfig != null) {
            throw new IllegalStateException("Code is duplicate.");
        }
    }

    public SqlReportConfig getSqlReportConfigById(final Integer id) {

        return jdbcTemplate.query(GET_BY_ID, new PreparedStatementSetter() {

            public void setValues(PreparedStatement preparedStatement) throws SQLException {
                preparedStatement.setInt(1, id);
            }
        }, new ResultSetExtractor<SqlReportConfig>() {
            public SqlReportConfig extractData(ResultSet resultSet) throws SQLException, DataAccessException {
                if (resultSet.next()) {
                    SqlReportConfig config = new SqlReportConfig();
                    config.setDbCode(resultSet.getString("db_code"));
                    config.setConfigCode(resultSet.getString("config_code"));
                    config.setReportName(resultSet.getString("report_name"));
                    config.setReportDesc(resultSet.getString("report_desc"));
                    config.setSqlContent(resultSet.getString("sql_content"));
                    config.setReportType(resultSet.getString("report_type"));
                    config.setStatus(resultSet.getInt("status"));
                    config.setCreateTime(resultSet.getLong("create_time"));
                    config.setCreateUser(resultSet.getString("create_user"));
                    config.setModifyTime(resultSet.getLong("modify_time"));
                    config.setModifyUser(resultSet.getString("modify_user"));
                    return config;
                }
                return null;
            }
        });
    }

    public SqlReportConfig getSqlReportConfigByCode(final String code) {

        return jdbcTemplate.query(GET_BY_CODE, new PreparedStatementSetter() {

            public void setValues(PreparedStatement preparedStatement) throws SQLException {
                preparedStatement.setString(1, code);
            }
        }, new ResultSetExtractor<SqlReportConfig>() {
            public SqlReportConfig extractData(ResultSet resultSet) throws SQLException, DataAccessException {
                if (resultSet.next()) {
                    SqlReportConfig config = new SqlReportConfig();
                    config.setDbCode(resultSet.getString("db_code"));
                    config.setConfigCode(resultSet.getString("config_code"));
                    config.setReportName(resultSet.getString("report_name"));
                    config.setReportDesc(resultSet.getString("report_desc"));
                    config.setSqlContent(resultSet.getString("sql_content"));
                    config.setReportType(resultSet.getString("report_type"));
                    config.setStatus(resultSet.getInt("status"));
                    return config;
                }
                return null;
            }
        });
    }

    public Integer updateSqlReportConfig(final SqlReportConfig config) {
        checkCode(config.getConfigCode());

        List<Object> args = new ArrayList<>();
        return jdbcTemplate.update(getUpdateSqlAndArgs(config, args), args.toArray());

    }

    public Integer deleteSqlReportConfig(final Integer id) {

        return jdbcTemplate.execute(new PreparedStatementCreator() {

            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                return connection.prepareStatement(DELETE_CONFIG);
            }
        }, new PreparedStatementCallback<Integer>() {

            public Integer doInPreparedStatement(PreparedStatement preparedStatement) throws SQLException, DataAccessException {
                preparedStatement.setInt(1, id);
                return preparedStatement.executeUpdate();
            }
        });
    }

    public List<SqlReportConfig> listSqlReportConfigPage(Map<String, String> parameters) {

        createTableNX();
        List<Object> args = new ArrayList<>();
        String sql = getQueryPageSqlAndArgs(parameters, args);
        return jdbcTemplate.query(sql, new RowMapper<SqlReportConfig>() {
            @Override
            public SqlReportConfig mapRow(ResultSet resultSet, int rowNum) throws SQLException {
                SqlReportConfig config = new SqlReportConfig();
                config.setId(resultSet.getInt("id"));
                config.setDbCode(resultSet.getString("db_code"));
                config.setConfigCode(resultSet.getString("config_code"));
                config.setReportName(resultSet.getString("report_name"));
                config.setReportDesc(resultSet.getString("report_desc"));
                config.setSqlContent(resultSet.getString("sql_content"));
                config.setReportType(resultSet.getString("report_type"));
                config.setStatus(resultSet.getInt("status"));
                config.setCreateUser(resultSet.getString("create_user"));
                config.setCreateTime(resultSet.getLong("create_time"));
                config.setModifyUser(resultSet.getString("modify_user"));
                config.setModifyTime(resultSet.getLong("modify_time"));
                return config;
            }
        }, args.toArray());
    }

    @DetermineDbCheck
    @Override
    public SqlRowSet getResultSet4ExecuteReportSql(SqlReportConfig config, Map<String, Object> params) {
        DataSourceContextHolder.setDbType(config.getDbCode());
        try {
            StringTemplateResourceLoader resourceLoader = new StringTemplateResourceLoader();
            Configuration cfg = Configuration.defaultConfiguration();
            GroupTemplate gt = new GroupTemplate(resourceLoader, cfg);
            Template t = gt.getTemplate(config.getSqlContent());
            t.binding(params);
            String sql = t.render();
            return jdbcTemplate.queryForRowSet(sql);
        } catch (Exception e) {
            throw new IllegalStateException(e);
        }
    }

    @DetermineDbCheck
    @Override
    public Integer getCount4ExecuteReportSql(SqlReportConfig config, Map<String, Object> params) {
        String sql = config.getSqlContent();
        if (sql == null || sql.trim().length() <= 0) {
            throw new IllegalArgumentException("Parse sql error.");
        }
        String newSql = "SELECT COUNT (1) FROM (".concat(sql).concat(")");
        try {
            StringTemplateResourceLoader resourceLoader = new StringTemplateResourceLoader();
            Configuration cfg = Configuration.defaultConfiguration();
            GroupTemplate gt = new GroupTemplate(resourceLoader, cfg);
            Template t = gt.getTemplate(newSql);
            t.binding(params);
            newSql = t.render();
            return jdbcTemplate.query(newSql, new ResultSetExtractor<Integer>() {
                public Integer extractData(ResultSet resultSet) throws SQLException, DataAccessException {
                    return resultSet.getInt(1);
                }
            });
        } catch (Exception e) {
            throw new IllegalStateException(e);
        }
    }
}
